﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Transactions;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Xoohoo.Extensions;
using Xoohoo.Models;
using Xoohoo.Modules.CMS.Models;

namespace Xoohoo.Modules.CMS.Repositories.SqlServerEntLibDAB
{
    public class CategoryRepository : ICategoryRepository
    {
        private readonly Database db;
        public CategoryRepository(string connectionStringName)
        {
            this.db = DatabaseFactory.CreateDatabase(connectionStringName);
        }

        #region ICategoryRepository Members

        public Category GetItem(Guid categoryID)
        {
            string sql = "select * from Category Where CategoryID=@CategoryID";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "CategoryID", DbType.String, categoryID.ToString());

            Category item = GetItem(cmd);

            return item;
        }

        public Category GetItem(string categoryName)
        {
            string sql = "select TOP 1 * from Category Where CategoryName=@CategoryName";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "CategoryName", DbType.String, categoryName);

            Category item = GetItem(cmd);

            return item;
        }

        public List<Category> GetList(Guid? parentID = null)
        {
            string sql;
            if (parentID.HasValue)
            {
                var parent = GetItem(parentID.Value);
                if (parent == null) return null;

                int displayOrder = GetDisplayOrderOfNextParentOrNextBrother(parent.DisplayOrder, parent.CategoryLevel);
                if (displayOrder != 0)
                    sql = "select * from Category Where DisplayOrder>={0} And DisplayOrder<{1} Order by DisplayOrder"
                        .FormatWith(parent.DisplayOrder, displayOrder);
                else
                    sql = "select * from Category Where DisplayOrder>={0} Order by DisplayOrder"
                        .FormatWith(parent.DisplayOrder, displayOrder);
            }
            else
            {
                sql = "select * from Category Order by DisplayOrder";
            }
            DbCommand cmd = db.GetSqlStringCommand(sql);
            IDataReader dr = db.ExecuteReader(cmd);

            var list = ProjectList(dr);
            dr.Close();
            return list;

        }

        public List<CategoryBasic> GetParentList(Guid categoryID)
        {
            return null;
        }

        public List<CategoryBasic> GetBasicList(Guid? parentID = null)
        {
            return null;
        }

        public void Save(CategoryInput categoryInput)
        {

        }

        public bool Remove(Guid categoryID)
        {
            using (TransactionScope ts = new TransactionScope())
            {
                //移除无限级分类步骤：

                //1、获取预删节点信息
                Category categoryToRemove = GetItem(categoryID);
                //当然，如果无法获取节点，属于无效操作；另外，超级管理员组和等待分配组不允许被删除
                if (categoryToRemove == null) return false;

                //2、节点包含子节点不允许删除
                string sql = "select top 1 1 from Category where ParentID=@CategoryID";
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "CategoryID", DbType.String, categoryID.ToString());
                if (db.ExecuteScalar(cmd) != null)
                    return false;

                //3、更新DisplayOrder大于预删节点DisplayOrder的节点
                sql = "Update Category Set DisplayOrder=DisplayOrder-1 Where DisplayOrder>@DisplayOrder";
                cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, categoryToRemove.DisplayOrder);
                db.ExecuteNonQuery(cmd);

                //6、删除节点
                sql = "Delete Category Where CategoryID=@CategoryID";
                cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "CategoryID", DbType.String, categoryID.ToString());
                db.ExecuteNonQuery(cmd);

                ts.Complete();
            }
            return true;
        }

        public bool Move(Guid categoryID, MovingTarget target)
        {
            using (TransactionScope ts = new TransactionScope())
            {
                string sql;
                DbCommand cmd;

                var category = GetItem(categoryID);

                //获取当前节点树 begin
                int curTreeCount;
                List<Guid> currTreeIDs = new List<Guid>();

                int displayOrderOfNextParentOrNextBrother = GetDisplayOrderOfNextParentOrNextBrother(category.DisplayOrder, category.CategoryLevel);

                if (displayOrderOfNextParentOrNextBrother == 0)
                {
                    sql = "Select CategoryID From Category Where  DisplayOrder>=@DisplayOrder";
                    cmd = db.GetSqlStringCommand(sql);
                    db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, category.DisplayOrder);

                }
                else
                {
                    sql = "Select CategoryID From Category Where DisplayOrder>=@DisplayOrder And DisplayOrder<@DOONPONB";
                    cmd = db.GetSqlStringCommand(sql);
                    db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, category.DisplayOrder);
                    db.AddInParameter(cmd, "DOONPONB", DbType.Int32, displayOrderOfNextParentOrNextBrother);
                }
                var dr = db.ExecuteReader(cmd);

                while (dr.Read())
                {
                    currTreeIDs.Add(dr.GetGuid(0));
                }
                dr.Close();

                curTreeCount = currTreeIDs.Count;

                //获取当前节点树 end

                if (MovingTarget.Up == target)
                {
                    //获取上一个兄弟节点 begin
                    sql = "select top 1 * from Category Where ParentID=@ParentID And DisplayOrder<@DisplayOrder Order by DisplayOrder desc";
                    cmd = db.GetSqlStringCommand(sql);
                    db.AddInParameter(cmd, "ParentID", DbType.Guid, category.ParentID);
                    db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, category.DisplayOrder);
                    dr = db.ExecuteReader(cmd);

                    Category targetCategory = ProjectBeforeRead(dr);

                    dr.Close();
                    //获取兄弟节点 end

                    if (targetCategory == null) return false;

                    //获取兄弟节点树的节点数
                    sql = "Select Count(1) From Category Where DisplayOrder>=@TDisplayOrder And DisplayOrder<@CDisplayOrder";
                    cmd = db.GetSqlStringCommand(sql);
                    db.AddInParameter(cmd, "CurTreeCount", DbType.Int32, curTreeCount);
                    db.AddInParameter(cmd, "TDisplayOrder", DbType.Int32, targetCategory.DisplayOrder);
                    db.AddInParameter(cmd, "CDisplayOrder", DbType.Int32, category.DisplayOrder);
                    int targetTreeCount = db.ExecuteScalarInt(cmd).Value;

                    //更新兄弟节点树的DisplayOrder
                    sql = "Update Category Set DisplayOrder = DisplayOrder+@CurTreeCount Where DisplayOrder>=@TDisplayOrder And DisplayOrder<@CDisplayOrder";
                    cmd = db.GetSqlStringCommand(sql);
                    db.AddInParameter(cmd, "CurTreeCount", DbType.Int32, curTreeCount);
                    db.AddInParameter(cmd, "TDisplayOrder", DbType.Int32, targetCategory.DisplayOrder);
                    db.AddInParameter(cmd, "CDisplayOrder", DbType.Int32, category.DisplayOrder);
                    db.ExecuteNonQuery(cmd);

                    //更新本节点树的DisplayOrder
                    sql = "Update Category Set DisplayOrder = DisplayOrder-@TargetTreeCount Where 1<>1 ";
                    foreach (var id in currTreeIDs)
                        sql += " Or CategoryID = '{0}'".FormatWith(id.ToString());
                    cmd = db.GetSqlStringCommand(sql);
                    db.AddInParameter(cmd, "TargetTreeCount", DbType.Int32, targetTreeCount);
                    db.ExecuteNonQuery(cmd);

                }
                else
                {
                    //获取下一个兄弟节点 begin
                    sql = "select top 1 * from Category Where ParentID=@ParentID And DisplayOrder>@DisplayOrder Order by DisplayOrder";
                    cmd = db.GetSqlStringCommand(sql);
                    db.AddInParameter(cmd, "ParentID", DbType.Guid, category.ParentID);
                    db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, category.DisplayOrder);

                    dr = db.ExecuteReader(cmd);
                    Category targetCategory = ProjectBeforeRead(dr);

                    dr.Close();
                    //获取兄弟节点 end

                    if (targetCategory == null) return false;

                    //获取兄弟节点树的节点数 begin
                    int displayOrderOfNextParentOrNextBrotherOfTarget = GetDisplayOrderOfNextParentOrNextBrother(targetCategory.DisplayOrder, targetCategory.CategoryLevel);

                    int targetTreeCount;
                    if (displayOrderOfNextParentOrNextBrotherOfTarget == 0)
                    {
                        sql = "Select Count(1) From Category Where DisplayOrder>=@DisplayOrder";
                        cmd = db.GetSqlStringCommand(sql);
                        db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, targetCategory.DisplayOrder);
                        targetTreeCount = db.ExecuteScalarInt(cmd).Value;
                    }
                    else
                    {
                        sql = "Select Count(1) From Category Where DisplayOrder>=@DisplayOrder And DisplayOrder<@TDisplayOrder";
                        cmd = db.GetSqlStringCommand(sql);
                        db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, targetCategory.DisplayOrder);
                        db.AddInParameter(cmd, "TDisplayOrder", DbType.Int32, displayOrderOfNextParentOrNextBrotherOfTarget);
                        targetTreeCount = db.ExecuteScalarInt(cmd).Value;
                    }
                    //获取兄弟节点树的节点数 end

                    //更新兄弟节点树的DisplayOrder
                    sql = "Update Category Set DisplayOrder = DisplayOrder-@CurTreeCount Where DisplayOrder>=@DisplayOrder And DisplayOrder<@TDisplayOrder";
                    cmd = db.GetSqlStringCommand(sql);
                    db.AddInParameter(cmd, "CurTreeCount", DbType.Int32, curTreeCount);
                    db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, targetCategory.DisplayOrder);
                    db.AddInParameter(cmd, "TDisplayOrder", DbType.Int32, targetCategory.DisplayOrder + targetTreeCount);
                    db.ExecuteNonQuery(cmd);


                    sql = "Update Category Set DisplayOrder = DisplayOrder+@TargetTreeCount Where 1<>1 ";
                    foreach (var id in currTreeIDs)
                        sql += " Or CategoryID = '{0}'".FormatWith(id.ToString());
                    cmd = db.GetSqlStringCommand(sql);
                    db.AddInParameter(cmd, "TargetTreeCount", DbType.Int32, targetTreeCount);
                    db.ExecuteNonQuery(cmd);

                }

                ts.Complete();
            }
            return true;
        }

        public bool Audit(Guid categoryID, bool isAuditing)
        {
            return ChangeState(categoryID, isAuditing, "IsAuditing");
        }

        public bool Recommend(Guid categoryID, bool isCommend)
        {
            return ChangeState(categoryID, isCommend, "IsCommend");
        }

        public bool Comment(Guid categoryID, bool allowComment)
        {
            return ChangeState(categoryID, allowComment, "AllowComment");
        }

        public bool Audit(IEnumerable<Guid> iDs, bool isAuditing)
        {
            return ChangeState(iDs, isAuditing, "IsAuditing");
        }

        public bool Recommend(IEnumerable<Guid> iDs, bool isCommend)
        {
            return ChangeState(iDs, isCommend, "IsCommend");
        }

        public bool Comment(IEnumerable<Guid> iDs, bool allowComment)
        {
            return ChangeState(iDs, allowComment, "AllowComment");
        }

        #endregion

        #region Private Methods

        private Category GetItem(DbCommand cmd)
        {
            IDataReader dr = db.ExecuteReader(cmd);
            Category item = ProjectBeforeRead(dr);
            dr.Close();
            return item;
        }
        private List<Category> ProjectList(IDataReader dr)
        {
            if (dr == null) return null;

            var list = new List<Category>();

            while (dr.Read())
            {
                list.Add(Project(dr));
            }
            return list;
        }
        private List<CategoryBasic> ProjectBasicList(IDataReader dr)
        {
            if (dr == null) return null;

            var list = new List<CategoryBasic>();

            while (dr.Read())
            {
                list.Add(new CategoryBasic
                {
                    CategoryID = dr.GetGuid(dr.GetOrdinal("CategoryID")),
                    CategoryName = dr.GetNullableString(dr.GetOrdinal("CategoryName")),
                    EntityName = dr.GetNullableString(dr.GetOrdinal("EntityName"))
                }
                );
            }
            return list;
        }
        private Category Project(IDataReader dr)
        {
            if (dr == null) return null;

            return new Category
            {
                CategoryID = dr.GetGuid(dr.GetOrdinal("CategoryID")),
                ParentID = dr.GetGuid(dr.GetOrdinal("ParentID")),
                CategoryName = dr.GetNullableString(dr.GetOrdinal("CategoryName")),
                CategoryLevel = dr.GetInt32(dr.GetOrdinal("CategoryLevel")),
                DisplayOrder = dr.GetInt32(dr.GetOrdinal("DisplayOrder")),
                CategoryIntro = dr.GetNullableString(dr.GetOrdinal("CategoryIntro")),
                CategoryDetail = dr.GetNullableString(dr.GetOrdinal("CategoryDetail")),
                CategoryImageUrl = dr.GetNullableString(dr.GetOrdinal("CategoryImageUrl")),
                Mode = (CategoryMode)dr.GetByte(dr.GetOrdinal("Mode")),
                Url = dr.GetNullableString(dr.GetOrdinal("Url")),
                IsCommend = dr.GetBoolean(dr.GetOrdinal("IsCommend")),
                IsAuditing = dr.GetBoolean(dr.GetOrdinal("IsAuditing")),
                AllowComment = dr.GetBoolean(dr.GetOrdinal("AllowComment")),
                EntityName = dr.GetNullableString(dr.GetOrdinal("EntityName")),
                Keywords = dr.GetNullableString(dr.GetOrdinal("Keywords")),
                Description = dr.GetNullableString(dr.GetOrdinal("Description"))
            };
        }
        private Category ProjectBeforeRead(IDataReader dr)
        {
            if (dr == null || !dr.Read()) return null;

            return Project(dr);
        }
        private int GetMaxDisplayOrder()
        {
            string sql = "Select Max(DisplayOrder) From Category";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            return db.ExecuteScalarInt(cmd) ?? 0;
        }
        private int GetMaxDisplayOrderInTree(Guid parentID)
        {
            int maxDisplayOrder;
            var parent = GetItem(parentID);
            if (parent == null)
                throw new Exception("或许尝试将节点加到不存在的父节点之上");

            //获取父节点之下的兄弟节点或更高层次的父节点(不是自己的父节点)的DisplayOrder
            int displayOrderOfNextParentOrNextBrother = GetDisplayOrderOfNextParentOrNextBrother(parent.DisplayOrder, parent.CategoryLevel);

            if (displayOrderOfNextParentOrNextBrother == 0)
            {
                string sql = "select MAX(DisplayOrder) from Category Where DisplayOrder>@DisplayOrder Or CategoryID=@CategoryID";
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, parent.DisplayOrder);
                db.AddInParameter(cmd, "CategoryID", DbType.String, parentID.ToString());
                maxDisplayOrder = db.ExecuteScalarInt(cmd).Value;
            }
            else
                maxDisplayOrder = displayOrderOfNextParentOrNextBrother - 1;

            return maxDisplayOrder;

        }
        private int GetDisplayOrderOfNextParentOrNextBrother(int displayOrder, int categoryLevel)
        {
            string sql = "select DisplayOrder from Category Where CategoryLevel<=@CategoryLevel And DisplayOrder>@DisplayOrder Order by DisplayOrder";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "CategoryLevel", DbType.Int32, categoryLevel);
            db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, displayOrder);
            return db.ExecuteScalarInt(cmd) ?? 0;
        }
        private int GetLevel(Guid pessionID)
        {
            string sql = "select CategoryLevel from Category Where CategoryID=@CategoryID";

            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "CategoryID", DbType.String, pessionID.ToString());

            return db.ExecuteScalarInt(cmd).Value;
        }
        private bool ChangeState(Guid categoryID, bool value, string field)
        {
            int v = value ? 1 : 0;
            string sql = "UPDATE Category Set {0}={1} Where CategoryID='{2}'".FormatWith(field, v, categoryID);
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.ExecuteNonQuery(cmd);
            return true;
        }
        private bool ChangeState(IEnumerable<Guid> ids, bool value, string field)
        {
            if (ids == null || ids.Count() == 0) return false;

            int v = value ? 1 : 0;
            StringBuilder sb = new StringBuilder();
            foreach (var id in ids)
                sb.Append(" Or CategoryID='{0}'".FormatWith(id));

            string sql = "UPDATE Category Set {0}={1} Where 1<>1 {2}".FormatWith(field, v, sb.ToString());
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.ExecuteNonQuery(cmd);
            return true;
        }
        #endregion

    }
}
